alter table [User]
add EmailSettingId int null
go
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_EmailSetting] FOREIGN KEY([EmailSettingId])
REFERENCES [dbo].[EmailSetting] ([EmailSettingId])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_EmailSetting]
GO
ALTER PROCEDURE [dbo].[spUser_Add]
(
	@UserName nvarchar(256),
	@FirstName varchar(100),
	@LastName varchar(100),
	@Email nvarchar(256),
	@IsAdmin bit,
	@Password nvarchar(128),
	@PasswordSalt nvarchar(128),
	@EmailSettingId int = null
)
AS
BEGIN
	set nocount on
	set xact_abort on
	
	declare @userGuid uniqueidentifier = NEWID()
	declare @appId uniqueidentifier
	declare @roleAdminId uniqueidentifier
	declare @roleAgentId uniqueidentifier
	
	select @appId = ApplicationId
	from aspnet_Applications
	
	select @roleAdminId = RoleId
	from aspnet_Roles
	where RoleName = 'admin'
	
	select @roleAgentId = RoleId
	from aspnet_Roles
	where RoleName = 'agent'
	
    begin transaction
		insert into aspnet_Users
		(ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate)
		values (@appId, @userGuid, @UserName, LOWER(@UserName), NULL, 0, GETDATE())
		
		insert into aspnet_Membership
		(ApplicationId, UserId, Password, PasswordFormat, PasswordSalt, MobilePIN, Email, LoweredEmail, 
			PasswordQuestion, PasswordAnswer, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, 
			LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart, Comment)
		values (@appId, @userGuid, @Password, 0, @PasswordSalt, NULL, @Email, LOWER(@Email), 
			NULL, NULL, 1, 0, GETDATE(), GETDATE(), GETDATE(), 
			0, 0, 0, 0, 0, NULL)
			
		IF (@isAdmin = 1)
			insert into aspnet_UsersInRoles
			(UserId, RoleId)
			values (@userGuid, @roleAdminId)
		else
			insert into aspnet_UsersInRoles
			(UserId, RoleId)
			values (@userGuid, @roleAgentId)
			
		INSERT INTO [User]
		(UserId, UserName, FirstName, LastName, Email, IsAdmin, Deleted, IsActive, EmailSettingId)
		VALUES (@userGuid, @UserName, @FirstName, @LastName, @Email, @IsAdmin, 0, 1, @EmailSettingId)
	COMMIT TRANSACTION
END
go
ALTER PROCEDURE [dbo].[spUser_Upd]
(
	@UserId int,
	
	@UserName nvarchar(256),
	@FirstName varchar(100),
	@LastName varchar(100),
	@Email nvarchar(256),
	@IsAdmin bit,
	@Deleted bit,
	@EmailSettingId int = null
)
AS
BEGIN
	set nocount on
	set xact_abort on
	
	declare @userGuid uniqueidentifier
	declare @appId uniqueidentifier
	declare @roleAdminId uniqueidentifier
	declare @roleAgentId uniqueidentifier
	
	declare @MsgErr varchar(100)
	set @MsgErr = ''
	
	if exists(select 1 from [User] where UserName = @UserName and Id <> @UserId)
	begin
		set @MsgErr = 'User Name already exists!'
		select @MsgErr as MsgErr
		return
	end
	
	if exists(select 1 from [User] where Email = @Email and Id <> @UserId)
	begin
		set @MsgErr = 'Email already exists!'
		select @MsgErr as MsgErr
		return
	end
	
	select @userGuid = UserId
	from [User]
	where Id = @UserId
	
	select @appId = ApplicationId
	from aspnet_Applications
	
	select @roleAdminId = RoleId
	from aspnet_Roles
	where RoleName = 'admin'
	
	select @roleAgentId = RoleId
	from aspnet_Roles
	where RoleName = 'agent'
	
    begin transaction
		update aspnet_Users
		set UserName = @UserName, LoweredUserName = LOWER(@UserName)
		where UserId = @userGuid
		
		update aspnet_Membership
		set Email = @Email, LoweredEmail = LOWER(@Email)
		where UserId = @userGuid
		
		delete 
		from aspnet_UsersInRoles 
		where UserId = @userGuid 
			
		if (@isAdmin = 1)
			insert into aspnet_UsersInRoles
			(UserId, RoleId)
			values (@userGuid, @roleAdminId)
		else
			insert into aspnet_UsersInRoles
			(UserId, RoleId)
			values (@userGuid, @roleAgentId)
							
		update [User]
		set UserName = @UserName, FirstName = @FirstName, LastName = @LastName, Email = @Email, 
			IsAdmin = @IsAdmin, Deleted = @Deleted, EmailSettingId = @EmailSettingId
		where Id = @UserId
	commit transaction
	
	select @MsgErr as MsgErr
END
go

